package com.culturalCenter.dataCenter.mapper.site;

import com.culturalCenter.dataCenter.customEntity.site.CustomSiteBooking;
import com.culturalCenter.dataCenter.entity.site.SiteBooking;
import com.culturalCenter.dataCenter.entity.site.SiteBookingMonth;
import com.culturalCenter.dataCenter.entity.site.SiteUsedCount;
import com.culturalCenter.dataCenter.mapper.base.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;

@Mapper
public interface SiteBookingMapper extends BaseMapper<SiteBooking> {

    /**
     * 根据月份查询排期（id设为bookingId）
     *
     * @param siteId
     * @param date
     *
     * @return
     */
    @Select("SELECT id AS bookingId, siteUser, applyUnit,purpose, beginTime, endTime, mobile, peopleNum, " +
            "IF(( SELECT COUNT( *) FROM cdc_site_alerts WHERE siteId = #{siteId} AND alertsTime >= beginTime AND alertsTime <= endTime )=0,0,1)AS `status` " +
            "FROM cdc_site_booking WHERE siteId = #{siteId} AND DATE_FORMAT( beginTime, '%Y-%m' ) = DATE_FORMAT( #{date}, '%Y-%m' ) AND status IN(2,3,4) AND examine=1 ORDER BY beginTime")
    List<CustomSiteBooking> selectSiteBookingByDate(@Param("siteId") String siteId, @Param("date") Date date);

    /**
     * 获得预约列表
     *
     * @param siteCode
     * @param date
     *
     * @return
     */
    @Select("SELECT id AS bookingId, purpose, siteUser, applyUnit, beginTime FROM cdc_site_booking WHERE siteCode = #{siteCode} " +
            "AND DATE_FORMAT(beginTime, '%Y-%m')=DATE_FORMAT(#{date}, '%Y-%m')AND `status` IN (1,5) AND examine=1 ORDER BY beginTime")
    List<CustomSiteBooking> selectSiteBookingByDateAndStatus(@Param("siteCode") String siteCode, @Param("date") Date date);

    /**
     * 获得月预约数
     *
     * @param siteCode
     * @param date
     *
     * @return
     */
    @Select("SELECT bookingNum FROM cdc_site_used_count WHERE siteCode=#{siteCode} AND year=DATE_FORMAT(#{date},'%Y') AND month=DATE_FORMAT(#{date},'%m')")
    Integer countBookingNumByDateAndStatus(@Param("siteCode") String siteCode, @Param("date") Date date);

    /**
     * 获得年预约数
     *
     * @param siteCode
     * @param date
     *
     * @return
     */
    @Select("SELECT bookingNum FROM cdc_site_used_count WHERE siteCode=#{siteCode} AND year=DATE_FORMAT(#{date},'%Y') AND month=0")
    Integer countTotalByDateAndStatus(@Param("siteCode") String siteCode, @Param("date") Date date);

    /**
     * 批量插入数据
     *
     * @param collect
     */
    void insertBatch(List<SiteBooking> collect);

    /**
     * 统计场地使用率
     *
     * @param beginTime
     * @param endTime
     *
     * @return
     */
    List<SiteUsedCount> countSiteUsedByDays(@Param("beginTime") Date beginTime, @Param("endTime") Date endTime);

    /**
     * 根据场地id统计场地闲置天数
     *
     * @param siteId
     * @param beginTime
     * @param endTime
     *
     * @return
     */
    @Select("SELECT SUM(IF(isUsed = 1, 1, 0)) AS unusedDays FROM " +
            "(SELECT (IF(COUNT(*) = 3, 1, 0))AS isUsed FROM cdc_site_booking " +
            "WHERE siteId = #{siteId} AND `status` = 0 AND beginTime > #{beginTime} AND beginTime < #{endTime})t")
    Integer countUnusedDays(@Param("siteId") String siteId, @Param("beginTime") Date beginTime, @Param("endTime") Date endTime);

    /**
     * 查询本月场地排期
     *
     * @return
     */
    @Select("SELECT * FROM cdc_site_booking WHERE DATE_FORMAT(beginTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m') AND examine=1")
    List<SiteBookingMonth> selectSiteBookingMonth();

    /**
     * 查询当前正在使用的场地总数
     *
     * @param status
     * @param date
     *
     * @return
     */
//    @Select("SELECT COUNT(*) FROM cdc_site_booking t1,cdc_site t2 " +
//            "WHERE beginTime <= #{date} AND endTime >= #{date} AND t1.`status` = #{status} AND t1.siteId=t2.refId")
    @Select("SELECT COUNT(*) FROM cdc_site_booking WHERE beginTime <= #{date} AND endTime >= #{date} AND examine = 1 AND status = #{status} AND siteCode !='' ")
    int selectCountByStatus(@Param("status") Integer status, @Param("date") Date date);

    @Select("SELECT COUNT(*) FROM cdc_site_booking WHERE beginTime >= #{beginTime} AND beginTime <= #{beginTime} AND endTime >= #{endTime} AND endTime <= #{endTime} AND status = #{status} AND examine = 1  AND siteCode !='' ")
    int searchCountByStatus(@Param("status") Integer status, @Param("beginTime") Date beginTime,@Param("endTime") Date endTime);

    /**
     * 场地当前状态
     *
     * @param siteCode
     *
     * @return
     */
    @Select("SELECT t1.status FROM cdc_site_booking t1, cdc_site t2 " +
            "WHERE t1.siteCode=#{siteCode} AND beginTime<=NOW() AND endTime>=NOW() AND t1.siteCode=t2.code AND t1.examine=1 AND t1.status NOT IN(-3,-1)")
    Integer selectStatusBySiteId(String siteCode);

    /**
     * 本月审核通过预约数
     *
     * @param siteId
     *
     * @return
     */
    @Select("SELECT SUM(IF(examine = 1, 1, 0)) AS approvedBookingNum FROM cdc_site_booking " +
            "WHERE siteId = #{siteId} AND DATE_FORMAT(beginTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')")
    Integer selectApprovedBookingNum(String siteId);

    /**
     * 本月预约签到数
     *
     * @param siteId
     *
     * @return
     */
    @Select("SELECT SUM(IF((STATUS = 3 OR STATUS = 4), 1, 0)) FROM cdc_site_booking_month " +
            "WHERE siteId = #{siteId} AND DATE_FORMAT(beginTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')")
    Integer selectSignInNum(String siteId);

    /**
     * 本月预约未签到数
     *
     * @param siteId
     *
     * @return
     */
    @Select("SELECT SUM(IF(STATUS IN (1,2,5), 1, 0)) FROM cdc_site_booking_month " +
            "WHERE siteId = #{siteId} AND DATE_FORMAT(beginTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')")
    Integer selectNoSignInNum(String siteId);

    /**
     * 场地分类预约时间段统计
     *
     * @param category
     * @param beginTime
     * @param endTime
     *
     * @return
     */
//    @Select("SELECT COUNT(*) FROM cdc_site_booking t1 JOIN cdc_site t2 ON t1.siteId = t2.refId AND t2.category = #{category} " +
//            "WHERE DATE_FORMAT(t1.beginTime, '%H') BETWEEN #{beginTime} AND #{endTime} AND t1.status!=-3 AND t1.status!=-1 AND t1.examine=1")
    Integer countBookingNumByCategoryAndTime(@Param("category") String category,
                                             @Param("beginTime") String beginTime,
                                             @Param("endTime") String endTime,
                                             @Param("startDate") String startDate,
                                             @Param("endDate") String endDate);

    /**
     * 场地分类为其他的预约时间段统计
     *
     * @param beginTime
     * @param endTime
     *
     * @return
     */
//    @Select("SELECT COUNT(*) FROM cdc_site_booking t1 JOIN cdc_site t2 ON t1.siteId = t2.refId AND (t2.category = '其他' OR t2.category IS NULL) " +
//            "WHERE DATE_FORMAT(t1.beginTime, '%H') BETWEEN #{beginTime} AND #{endTime} AND t1.status!=-3 AND t1.status!=-1 AND t1.examine=1")
    Integer countBookingNumByCategoryIsOtherAndTime(@Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("startDate") String startDate,
                                                    @Param("endDate") String endDate);

    /**
     * 查询今日预约
     *
     * @return
     */
    @Select("SELECT * FROM cdc_site_booking WHERE DATE_FORMAT(beginTime,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')")
    List<SiteBooking> selectTodayBookings();

    /**
     * 更新booking状态
     *
     * @param siteBooking
     */
    @Update("UPDATE cdc_site_booking SET status=#{status} WHERE id=#{id}")
    void updateStatus(SiteBooking siteBooking);

    /**
     * 该场地使用天数
     *
     * @param siteId
     * @param beginTime
     * @param endTime
     *
     * @return
     */
    int countUsedDays(@Param("siteId") String siteId, @Param("beginTime") Date beginTime, @Param("endTime") Date endTime);

    Integer newSearchCountByStatus(@Param("startTime") Date startTime, @Param("endTime") Date endTime);
}
